﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class HkcdtDAL:IHkcdtDAL
    {
        public int Add(HkcdtInfo dt)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dt.Dtnum),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dt.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dt.Tianbao),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, dt.Kday)
            };
            return (DALHelper.RunCommand(@"insert into dd.HKCDT (dtnum, banzu, tianbao, kday) values (:dtnum, :banzu, :tianbao, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'))", prams));

        }
        public int Update(string dtnum, decimal kn)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn)
            };
            return (DALHelper.RunCommand(@"update dd.HKCDT set kn = :kn where dtnum = :dtnum", prams));

        }
        public int Del(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.HKCDT where DTNUM = :dtnum", prams));
        }
        public DataSet Select(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.HKCDT where dtnum = :dtnum", prams, "jl"));
        }
        public DataSet SelectAll()
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select dtnum, kday, banzu, tianbao, kn from dd.HKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jlall"));

        }
        public string KuKC(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(kn) from dd.HKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
    }
}
